﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.IO;
using System.Web.SessionState;
using NetWing.Model;
using NetWing.Bll;
using Omu.ValueInjecter;
using NetWing.BPM.Core;
using NetWing.BPM.Core.Bll;
using NetWing.Common;
using NetWing.Common.Data;
using NetWing.Common.Data.SqlServer;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using NetWing.Common.Excel;
using NetWing.Common.IO;
using NetWing.Common.IO.DirFile;
using NetWing.Common.Data.Filter;
using NetWing.Common.Pinyin;//引用汉字转简拼类
using NetWing.BPM.Core.Model;
using NetWing.BPM.Core.Dal;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System.Text.RegularExpressions;

namespace NetWing.BPM.Admin.JydUser.ashx
{
    /// <summary>
    /// jydUserycl 的摘要说明
    /// </summary>
    public class jydUserycl : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            int k;
            var json = HttpContext.Current.Request["json"];
            var rpm = new RequestParamModel<JydUserModel>(context) { CurrentContext = context };
            if (!string.IsNullOrEmpty(json))
            {
                rpm = JSONhelper.ConvertToObject<RequestParamModel<JydUserModel>>(json);
                rpm.CurrentContext = context;
            }


            else //不以json方式提交 没有带参数?json=的时候 必须带 ?action=
            {
                rpm.Action = context.Request["action"];
            }

            switch (rpm.Action)
            {
                case "add":
                    JydUserModel a = new JydUserModel();
                    a.InjectFrom(rpm.Entity);
                    a.KeyId = rpm.KeyId;
                    a.UserName = Pinyin.GetCodstring(a.comname).ToLower();//汉字转简拼并小写
                    //判断加入tell字段为空则把用户手机作为电话
                    if (string.IsNullOrEmpty(a.tell))//假如电话为空则把手机号设为企业电话
                    {
                        a.tell = a.mobile;
                    }
                    //判断不要重复录入公司名称
                    int c = (int)SqlEasy.ExecuteScalar("select count(keyid) from jydUser where comname='" + a.comname + "'");
                    if (c > 0)//说明数据库里有
                    {
                        context.Response.Write("-99");//-99数据里已经有相同公司名称
                    }
                    else
                    {
                        context.Response.Write(JydUserBll.Instance.Add(a));

                    }

                    LogModel logxh = new LogModel();
                    logxh.BusinessName = SysVisitor.Instance.cookiesUserId + "添加客户成功";
                    logxh.OperationIp = context.Request.ServerVariables["REMOTE_ADDR"];
                    logxh.OperationTime = DateTime.Now;
                    logxh.PrimaryKey = "";
                    logxh.UserId = int.Parse(SysVisitor.Instance.cookiesUserId);
                    logxh.SqlText = "";
                    logxh.TableName = "jydOrder";
                    logxh.note = "客户添加成功";
                    logxh.OperationType = (int)OperationType.Update;
                    LogDal.Instance.Insert(logxh);

                    //context.Response.Write(JydUserBll.Instance.Add(rpm.Entity));
                    break;
                case "edit":
                    JydUserModel d = new JydUserModel();
                    d.InjectFrom(rpm.Entity);
                    d.KeyId = rpm.KeyId;
                    context.Response.Write(JydUserBll.Instance.Update(d));
                    break;
                case "export":
                    //string fields = rpm.fields;
                    string fields = rpm.CurrentContext.Request["fields"];
                    string tablename = TableConvention.Resolve(typeof(JydUserModel));//得到表名
                    DataTable xlsDt = SqlEasy.ExecuteDataTable("select " + fields + " from " + tablename + "");
                    ExcelHelper.NPIOtoExcel(xlsDt, HttpContext.Current.Server.MapPath("\\upload\\excel\\" + tablename + ".xls"));
                    context.Response.Write("{\"status\":\"ok\",\"filename\":\"" + tablename + ".xls\"}");
                    break;
                case "inport"://从Excel导入到数据库
                    if (context.Request["REQUEST_METHOD"] == "OPTIONS")
                    {
                        context.Response.End();
                    }
                    SaveFile("~/temp/", context);
                    break;
                case "delete":
                    context.Response.Write(JydUserBll.Instance.Delete(rpm.KeyId));
                    break;
                case "alldel"://2017-04-05新增的功能 批量删除删除结果返回删除条数
                    context.Response.Write(NetWing.Dal.JydUserDal.Instance.Delete(rpm.KeyIds));
                    break;
                case "sousu":
                    string sousu = context.Request["q"];
                    Regex regex = new Regex("[a-z]");
                    DataTable table = null;
                    if (!string.IsNullOrEmpty(sousu))
                    {
                        if (regex.IsMatch(sousu))
                        {
                            table = SqlEasy.ExecuteDataTable("select top 20 * from jyduser where username like '%" + sousu + "%' and fenlei='原材料供应商' order by keyid desc");
                        }
                        else
                        {
                            table = SqlEasy.ExecuteDataTable("select top 20 * from jyduser where comname like '%" + sousu + "%' and fenlei='原材料供应商' order by keyid desc");
                        }
                        context.Response.Write(JSONhelper.ToJson(table));
                    }
                    else
                    {
                        table = SqlEasy.ExecuteDataTable("select top 20 * from jyduser where fenlei='原材料供应商' order by keyid desc");
                        context.Response.Write(JSONhelper.ToJson(table));
                    }
                    break;
                default:
                    //string sqlwhere = "(deptid in (" + SysVisitor.Instance.cookiesUserDepId + "))";
                    //string sqlwhere = "";
                    //if (SysVisitor.Instance.cookiesIsAdmin == "True")
                    //{ 判断是否是超管如果是超管理，所有显示
                    string sqlwhere = " 1=1 ";//如果是超管则不显示
                    //}
                    if (!string.IsNullOrEmpty(rpm.Filter))//如果筛选不为空
                    {
                        string str = " and " + FilterTranslator.ToSql(rpm.Filter);
                        //JObject jo = (JObject)JsonConvert.DeserializeObject(rpm.Filter);
                        //string dtzone = jo["addtime"].ToString();
                        //string dtzone_en = jo["desiredtime"].ToString();
                        sqlwhere = sqlwhere + str;
                    }
                    //string sqlwheree = "";
                    //if (SysVisitor.Instance.cookiesIsAdmin == "True")
                    //{ //判断是否是超管如果是超管理，所有显示
                    //    sqlwheree = " 1=1 ";//如果是超管则不显示
                    //}
                    //if (!string.IsNullOrEmpty(rpm.Filtere))//如果筛选不为空
                    //{
                    //    string str = " and " + FilterTranslator.ToSql(rpm.Filtere);
                    //    //JObject jo = (JObject)JsonConvert.DeserializeObject(rpm.Filter);
                    //    //string dtzone = jo["addtime"].ToString();
                    //    //string dtzone_en = jo["desiredtime"].ToString();
                    //    sqlwheree = sqlwheree + str;
                    //}

                    if (!string.IsNullOrEmpty(context.Request["q"]))//如果服务端传来了参数q
                    {
                        sqlwhere = sqlwhere + " and (realname like '%" + context.Request["q"] + "%' or comname like '%" + context.Request["q"] + "%' or username like '%" + context.Request["q"] + "%')";
                    }
                    sqlwhere = sqlwhere + " and fenlei='直接客户'";
                    string sort = rpm.Sort;
                    if (sort == null)
                    {
                        sort = "keyid desc";
                    }
                    //TableConvention.Resolve(typeof(MJUserModel)) 转换成表名
                    var pcpstr = new ProcCustomPage(TableConvention.Resolve(typeof(JydUserModel)))
                    {
                        PageIndex = rpm.Pageindex,
                        PageSize = rpm.Pagesize,
                        //OrderFields = rpm.Sort,
                        OrderFields = sort,
                        WhereString = FilterTranslator.ToSql(rpm.Filter)
                    };
                    int recordCount;
                    DataTable dt = DbUtils.GetPageWithSp(pcpstr, out recordCount);
                    dt.Columns.Add("jexx", Type.GetType("System.String"));//总金额
                    dt.Columns.Add("yufuk", Type.GetType("System.String"));//预付款
                    dt.Columns.Add("myysf", Type.GetType("System.String"));//应付款
                    decimal je = 0;
                    decimal yufuk = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        string strJexx = " select SUM(yfk+wk) from Psi_BuyDetails where unitname='"+dr["comname"].ToString()+"' and  " + FilterTranslator.ToSql(rpm.Filtere) + "  and examine_status=2";
                        dr["jexx"] = SqlEasy.ExecuteScalar(strJexx) == DBNull.Value ? "0" : SqlEasy.ExecuteScalar(strJexx).ToString();


                        je += Convert.ToDecimal(dr["jexx"].ToString());
                        string yufukstr = " select SUM(yfk) from Psi_BuyDetails where unitname='" + dr["comname"].ToString() + "' and  " + FilterTranslator.ToSql(rpm.Filtere) + "  and examine_status=2";
                        dr["yufuk"] = SqlEasy.ExecuteScalar(yufukstr) == DBNull.Value ? "0" : SqlEasy.ExecuteScalar(yufukstr).ToString();
                        yufuk += Convert.ToDecimal(dr["yufuk"].ToString());
                        dr["myysf"] = SqlEasy.ExecuteScalar("select SUM(wk) from Psi_BuyDetails where unitname='" + dr["comname"].ToString() + "' and " + FilterTranslator.ToSql(rpm.Filtere) + "  and examine_status=2").ToString();
                    }
                    ////统计库存总量
                    //decimal stockAll = decimal.Parse(string.IsNullOrEmpty( dt.Compute("Sum(jexx)", "").ToString()) ? "0" : dt.Compute("Sum(jexx)", "").ToString());
                    //////库存总价值到前台去润色
                    //////计算前台总库存额
                    //decimal stockMoneyAll = 0;
                    //foreach (DataRow dr in dt.Rows)
                    //{
                    //    //库存量*进价
                    //    stockMoneyAll = stockMoneyAll + decimal.Parse(dr["jexx"].ToString()) * decimal.Parse(dr["jexx"].ToString());

                    //}
                    //List<dynamic> footerlist = new List<dynamic> { new { postcode = ":", jexx = stockAll, fax = "总金额:", yufuk = stockMoneyAll } };//因为不方便这里用时间显示库存金额


                    List<dynamic> footerlist = new List<dynamic> { new { mobile = "预付款:", fenlei = yufuk, jexx = "总金额:", yufuk = je} };
                    context.Response.Write(JSONhelper.FormatJSONForEasyuiDataGrid(recordCount, dt, footerlist));
                    //context.Response.Write(JydUserBll.Instance.GetJson(rpm.Pageindex, rpm.Pagesize, rpm.Filter, rpm.Sort, rpm.Order));
                    break;
            }
        }


        /// <summary>
        /// 文件保存操作
        /// </summary>
        /// <param name="basePath"></param>
        private void SaveFile(string basePath, HttpContext context)
        {
            var name = string.Empty;
            basePath = (basePath.IndexOf("~") > -1) ? context.Server.MapPath(basePath) :
            basePath;
            HttpFileCollection files = context.Request.Files;

            if (!Directory.Exists(basePath))//如果文件夹不存在创建文件夹
                Directory.CreateDirectory(basePath);
            //清空temp文件夹
            DirFileHelper.ClearDirectory(basePath);

            var suffix = files[0].ContentType.Split('/');
            var _suffix = suffix[1].Equals("jpeg", StringComparison.CurrentCultureIgnoreCase) ? "" : suffix[1];
            var _temp = System.Web.HttpContext.Current.Request["name"];

            if (!string.IsNullOrEmpty(_temp))
            {
                name = _temp;
            }
            else
            {
                Random rand = new Random(24 * (int)DateTime.Now.Ticks);
                name = rand.Next() + "." + _suffix;
            }

            var full = basePath + name;
            files[0].SaveAs(full);

            DataTable dt = NPOIHelper.ImportExceltoDt(full);
            string connectionString = SqlEasy.connString;
            SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
            sqlbulkcopy.DestinationTableName = TableConvention.Resolve(typeof(JydUserModel));//数据库中的表名
                                                                                             //自定义的datatable和数据库的字段进行对应  
                                                                                             //sqlBC.ColumnMappings.Add("id", "tel");  
                                                                                             //sqlBC.ColumnMappings.Add("name", "neirong");  
            int k = dt.Rows.Count - 1;                                                                       //注意一个问题，最后一列是字段数
            for (int i = 0; i < (dt.Columns.Count - 1); i++)
            {
                sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName.ToString(), dt.Columns[i].ColumnName.ToString());

            }
            var _result = "";
            try
            {
                sqlbulkcopy.WriteToServer(dt);
                _result = "{\"msg\" : \"导入数据库成功!\", \"result\" : " + k + ", \"filename\" : \"" + name + "\"}";
            }
            catch (Exception)
            {
                _result = "{\"msg\" : \"导入失败,可能模板不对，或其他原因，建议导出数据作为模板重新处理。注意导入没有校验数据重复功能。请人工校验数据!\", \"result\" : 0, \"filename\" : \"" + name + "\"}";
                //throw;
            }



            System.Web.HttpContext.Current.Response.Write(_result);

        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}